package homework;
//作业标题(799)
//有一个图书管理系统，请根据要求写出对应的sql
//
//作业内容
//有一个图书管理系统，包含学生和图书信息，且图书可以进行分类，学生可以在一个时间范围内借阅图书，并在这个时间范围内归还图书。表和表关系如下：

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

class SqlClass {
    private DataSource m_dataSource;
    private Connection m_connection;
    private PreparedStatement m_statement;
    private ResultSet m_resultSet;

    public SqlClass(String databaseName, String characterEncoding, boolean useSSl) throws SQLException {
        this.m_dataSource = new MysqlDataSource();
        ((MysqlDataSource)this.m_dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/" + databaseName + "?characterEncoding=" + characterEncoding + "&useSSL=" + useSSl);
        ((MysqlDataSource)this.m_dataSource).setUser("root");
        ((MysqlDataSource)this.m_dataSource).setPassword("root");

        this.m_connection = this.m_dataSource.getConnection();
        this.m_statement = null;
        this.m_resultSet = null;
    }

    public int mysqlUpdata(String sql) throws SQLException {
        this.m_statement = this.m_connection.prepareStatement(sql);
        return this.m_statement.executeUpdate();
    }

    public ResultSet mysqlSelect(String sql) throws SQLException {
        this.m_statement = this.m_connection.prepareStatement(sql);
        this.m_resultSet = this.m_statement.executeQuery();
        return this.m_resultSet;
    }
    public void close() throws SQLException {
        if(this.m_resultSet != null) this.m_resultSet.close();
        if(this.m_statement != null) this.m_statement.close();
        if(this.m_connection != null) this.m_connection.close();
    }
}
public class h799 {

    public static void main(String[] args) throws SQLException {
        SqlClass sqlClass = new SqlClass("mysql","UTF-8",false);
        //DROP DATABASE IF EXISTS ebook;
        sqlClass.mysqlUpdata("drop database if exists ebook");

        //CREATE DATABASE ebook CHARACTER SET 'utf8mb4';
        sqlClass.mysqlUpdata("create database ebook character set 'utf8mb4'");

        //USE ebook;
        sqlClass.mysqlUpdata("use ebook");

        //DROP TABLE IF EXISTS category;
        sqlClass.mysqlUpdata("drop table if exists category");

        //CREATE TABLE category (
        //
        // id int(11) PRIMARY KEY AUTO_INCREMENT,
        //
        // name varchar(20)
        //
        //);
        sqlClass.mysqlUpdata("create table category(" +
                "id int(11) primary key auto_increment," +
                "name varchar(20)" +
                ");");

        //INSERT INTO category VALUES (1, '历史');
        sqlClass.mysqlUpdata("insert into category values(1, '历史')");

        //INSERT INTO category VALUES (2, '艺术');
        sqlClass.mysqlUpdata("insert into category values(2, '艺术')");

        //INSERT INTO category VALUES (3, '计算机');
        sqlClass.mysqlUpdata("insert into category values(3, '计算机')");

        //INSERT INTO category VALUES (4, '数学');
        sqlClass.mysqlUpdata("insert into category values(4, '数学')");

        //INSERT INTO category VALUES (5, '小说');
        sqlClass.mysqlUpdata("insert into category values(5, '小说')");


        //DROP TABLE IF EXISTS student;
        sqlClass.mysqlUpdata("drop table if exists student");

        //CREATE TABLE student (
        // id int(11) PRIMARY KEY AUTO_INCREMENT,
        // name varchar(20)
        //);
        sqlClass.mysqlUpdata("create table student(" +
                "id int(11) primary key auto_increment," +
                "name varchar(20)" +
                ");");

        //INSERT INTO student VALUES (1, '王昭君');
        sqlClass.mysqlUpdata("insert into student values(1, '王昭君')");

        //INSERT INTO student VALUES (2, '李白');
        sqlClass.mysqlUpdata("insert into student values(2, '李白')");

        //INSERT INTO student VALUES (3, '貂蝉');
        sqlClass.mysqlUpdata("insert into student values(3, '貂蝉')");

        //INSERT INTO student VALUES (4, '小乔');
        sqlClass.mysqlUpdata("insert into student values(4, '小乔')");

        //INSERT INTO student VALUES (5, '韩信');
        sqlClass.mysqlUpdata("insert into student values(5, '韩信')");

        sqlClass.close();
    }
}
